﻿SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[bht_NemoTranslates](
	[trans_id] [int] IDENTITY(1,1) NOT NULL,
	[record_id] [int] NOT NULL,
	[field_id] [int] NOT NULL,
	[translate] [nvarchar](max) NOT NULL,
	[lang_code] [nchar](5) NULL,
	[mtime] [datetime] NOT NULL,
	[muser] [int] NOT NULL,
 CONSTRAINT [PK_bht_NemoTranslates] PRIMARY KEY CLUSTERED 
(
	[trans_id] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[bht_NemoTranslates] ADD  CONSTRAINT [DF_bht_NemoTranslates_ctime]  DEFAULT (getdate()) FOR [mtime]
GO

CREATE FUNCTION [dbo].[bhf_NemoTranslateGet](
	@FieldId		INT,
	@Record			INT,
	@Language		NCHAR(5),
	@DefaultValue	NVARCHAR(MAX) = ''
)
RETURNS NVARCHAR(MAX)
AS
BEGIN

	RETURN
		COALESCE(
			(SELECT TOP 1
				translate
			FROM
				[bht_NemoTranslates]
			WHERE
				field_id		= @FieldId
				AND record_id	= @Record
				AND lang_code	= @Language), @DefaultValue)

END

GO



CREATE PROCEDURE [dbo].[bhp_NemoTranslateGetLanguages]
AS
BEGIN
	
	SELECT DISTINCT lang_code FROM [bht_NemoTranslates]

END


GO

CREATE PROCEDURE [dbo].[bhp_NemoTranslateGet] (
	@GroupId	INT,
	@FieldId	INT,
	@RecordId	INT,
	@Language	NCHAR(5)
)
AS
BEGIN
	
	DECLARE
		@Val		NVARCHAR(MAX)
		
	SELECT @Val = dbo.[bhf_NemoTranslateGet](@FieldId,@RecordId,@Language,'')
	
	SELECT @Val

END


GO

CREATE PROCEDURE [dbo].[bhp_NemoTranslateGetOryginal] (
	@GroupId	INT,
	@FieldId	INT,
	@RecordId	INT
)
AS
BEGIN
	
	DECLARE
		@Q			NVARCHAR(1000),
		@ColName	NVARCHAR(100),
		@Table		NVARCHAR(100)
		
	SELECT
		@ColName = name
	FROM
		n_Tree 
	WHERE
		id = @FieldId
		
	SELECT
		@Table = name
	FROM
		n_Tree 
	WHERE
		id = @GroupId
		
	SET @Q = 'SELECT ' + @ColName + ' FROM ' + @Table + ' WHERE idx = ' + CAST(@RecordId AS NVARCHAR(10))
	EXEC sp_executesql @Q

END


GO
